using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Core;
using IglooCoder.Commons.WcfNhibernate;
using NHibernate;
using NHibernate.Criterion;
using NHibernate.Linq;
using NHibernate.SqlCommand;
using Dapper;
using NHibernate.Transform;

namespace Infrastucture
{
    public class SalesReturnRepository : BaseNHibernateRepository<SalesReturn>
    {
        public SalesReturnRepository(ISessionStorage sessionStorage)
            : base(sessionStorage)
        {
        }

        public string GetMaxNumber(string code, string kodeAwal)
        {
            string res = string.Empty;
            var lenKode = kodeAwal.Length + 11 + code.Length;
            var query = Session.CreateSQLQuery("Select Max(SRNo) as Number from SalesReturn  where SRNo like'" + kodeAwal + "%'  AND SRNo like'%" + code + "%'  AND  Len(SRNo)=" + lenKode).AddScalar("Number", NHibernateUtil.String);
            var objRes = query.List()[0];
            if (objRes != null) res = objRes.ToString();
            return res;
        }

        public void UpdateStatus(string SRNo, int Status)
        {
            ISQLQuery command =
            Session.CreateSQLQuery("Update SalesReturn set Status=" + Status + " Where SRNo='" + SRNo + "'");
            int res = command.ExecuteUpdate();
        }

        public void UpdateEmailStatus(string SRNo)
        {
            ISQLQuery command =
            Session.CreateSQLQuery("Update SalesReturn set HasSent=1 Where SRNo='" + SRNo + "'");
            int res = command.ExecuteUpdate();
        }

        public override IList<SalesReturn> GetDataPaged(int page, int pageSize, string sortProperty, string sortType)
        {
            var Results = new List<SalesReturn>();

            using (ITransaction tx = Session.BeginTransaction())
            {
                Order order;
                if (sortType.ToUpper() == "ASC")
                    order = new Order(sortProperty, true);
                else
                    order = new Order(sortProperty, false);

                ICriteria criteria = Session.CreateCriteria<SalesReturn>()
                    .CreateAlias("SalesInvoice", "si", JoinType.LeftOuterJoin)
                    .CreateAlias("Customer", "c", JoinType.InnerJoin)
                    .CreateAlias("CostCenter", "cc", JoinType.InnerJoin)
                    .CreateAlias("Warehouse", "w", JoinType.InnerJoin)
                    .SetProjection((Projections.ProjectionList()
                               .Add(Projections.Property("SRNo"))
                               .Add(Projections.Property("SRDate"))
                               .Add(Projections.Property("Description"))
                               .Add(Projections.Property("Status"))
                               .Add(Projections.Property("si.SINo"))
                               .Add(Projections.Property("c.CustomerName"))
                               .Add(Projections.Property("cc.Description"))
                               .Add(Projections.Property("w.KodeWarehouse"))
                     ))
                    .AddOrder(order)
                    .SetFirstResult((page - 1) * pageSize).SetMaxResults(pageSize)
                    ;

                var criteriaCount = Session.CreateCriteria<SalesReturn>()
                    .CreateAlias("SalesInvoice", "si", JoinType.LeftOuterJoin)
                    .CreateAlias("Customer", "c", JoinType.InnerJoin)
                    .CreateAlias("CostCenter", "cc", JoinType.InnerJoin)
                    .CreateAlias("Warehouse", "w", JoinType.InnerJoin)
                    .SetProjection(Projections.RowCountInt64());

                IList Res = Session.CreateMultiCriteria().Add(criteria).Add(criteriaCount).List();

                foreach (object o in (IList)Res[0])
                {
                    var salesReturn = new SalesReturn();
                    salesReturn.SRNo = (o as object[])[0].ToString();
                    salesReturn.SRDate = (DateTime) (o as object[])[1];
                    salesReturn.Description = (string) (o as object[])[2];
                    salesReturn.Status = (int) (o as object[])[3];
                    salesReturn.SalesInvoice = new SalesInvoice(){SINo = (string) (o as object[])[4]};
                    salesReturn.Customer = new Customer(){CustomerName = (string) (o as object[])[5]};
                    salesReturn.CostCenter = new CostCenter(){Description = (string) (o as object[])[6]};
                    salesReturn.Warehouse = new Warehouse(){KodeWarehouse = (string) (o as object[])[7]};
                    Results.Add(salesReturn);
                }

                RecordCount = (long)((IList)Res[1])[0];
                tx.Commit();
            }


            //Using Dapper
            //using (var conn = Session.Connection)
            //{
            //    var sb = new StringBuilder();
            //    sb.Append("Select T.*,si.*,c.*,cc.*,w.*,c.CustomerName,cc.Description,w.WarehouseName from (");
            //    sb.Append("Select sr.*,ROW_NUMBER() OVER (ORDER BY SRDate) rownum from SalesReturn sr ");
            //    sb.Append(") T ");
            //    sb.Append("Inner Join SalesInvoice si on T.SINo = si.SINO ");
            //    sb.Append("Inner Join Customer c on c.CustomerId= T.CustomerId ");
            //    sb.Append("Inner Join CostCenter cc on cc.CostCenterID= T.CostCenterID ");
            //    sb.Append("Inner Join Warehouse w on w.KodeWarehouse= T.KodeWarehouse ");
            //    sb.AppendFormat("Where rownum >{0} and rownum<{1}", (page - 1) * pageSize, (page * pageSize + 1));
            //    var salesReturns = conn.Query<SalesReturn, SalesInvoice, Customer, CostCenter, Warehouse, SalesReturn>(sb.ToString(),
            //                                                                                    (sr, si, c, cost, w) =>
            //                                                                                    {
            //                                                                                        sr.SalesInvoice = si;
            //                                                                                        sr.Customer = c;
            //                                                                                        sr.CostCenter =
            //                                                                                            cost;
            //                                                                                        sr.Warehouse = w;
            //                                                                                        return sr;
            //                                                                                    }, splitOn: "SINo,CustomerId,CostCenterId,KodeWarehouse");

            //    Results = salesReturns.ToList();
            //}

          

          

            return Results;

        }



    }
}